package com.cgm.controller.utils;

import java.io.BufferedOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.util.List;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.*;

import com.cgm.service.dto.StudentDTO;

/**
 * Created by Administrator on 2017/5/3.
 */

@SuppressWarnings("serial")
public class OutputExcel extends HttpServlet {

	protected void doGet(HttpServletRequest request,
			HttpServletResponse response) throws ServletException, IOException {
		this.doPost(request, response);
	}

	protected void doPost(HttpServletRequest request,
			HttpServletResponse response,List<StudentDTO> list) throws ServletException, IOException {
		System.out.println("helloworld");

		String fileName = "导出Excel.xls";
		fileName = new String(fileName.getBytes("GBK"), "iso8859-1");
		response.reset();
		response.setHeader("Content-Disposition",
				"attachment;filename=" + fileName);// 指定下载的文件名
		response.setContentType("application/vnd.ms-excel");
		response.setHeader("Pragma", "no-cache");
		response.setHeader("Cache-Control", "no-cache");
		response.setDateHeader("Expires", 0);
		OutputStream output = response.getOutputStream();
		BufferedOutputStream bufferedOutPut = new BufferedOutputStream(output);

		// 定义单元格报头
		String worksheetTitle = "Excel导出Student信息";

		HSSFWorkbook wb = new HSSFWorkbook();

		// 创建单元格样式
		HSSFCellStyle cellStyleTitle = wb.createCellStyle();
		// 指定单元格居中对齐
		cellStyleTitle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		// 指定单元格垂直居中对齐
		cellStyleTitle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
		// 指定当单元格内容显示不下时自动换行
		cellStyleTitle.setWrapText(true);
		// ------------------------------------------------------------------
		HSSFCellStyle cellStyle = wb.createCellStyle();
		// 指定单元格居中对齐
		cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		// 指定单元格垂直居中对齐
		cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
		// 指定当单元格内容显示不下时自动换行
		cellStyle.setWrapText(true);
		// ------------------------------------------------------------------
		// 设置单元格字体
		HSSFFont font = wb.createFont();
		font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
		font.setFontName("宋体");
		font.setFontHeight((short) 200);
		cellStyleTitle.setFont(font);

		// 工作表名
		String id = "id";
		String name = "name";
		String age = "age";
		String address = "address";
		String tel = "tel";
		String sex = "sex";

		HSSFSheet sheet = wb.createSheet();
		ExportExcelUtil exportExcel = new ExportExcelUtil(wb, sheet);
		// 创建报表头部
		exportExcel.createNormalHead(worksheetTitle, 6);
		// 定义第一行
		HSSFRow row1 = sheet.createRow(1);
		HSSFCell cell1 = row1.createCell((short)0);

		// 第一行第一列

		cell1.setCellStyle(cellStyleTitle);
		cell1.setCellValue(new HSSFRichTextString(id));
		// 第一行第er列
		cell1 = row1.createCell((short) 1);
		cell1.setCellStyle(cellStyleTitle);
		cell1.setCellValue(new HSSFRichTextString(name));

		// 第一行第san列
		cell1 = row1.createCell((short)2);
		cell1.setCellStyle(cellStyleTitle);
		cell1.setCellValue(new HSSFRichTextString(age));

		// 第一行第si列
		cell1 = row1.createCell((short)3);
		cell1.setCellStyle(cellStyleTitle);
		cell1.setCellValue(new HSSFRichTextString(address));

		// 第一行第wu列
		cell1 = row1.createCell((short)4);
		cell1.setCellStyle(cellStyleTitle);
		cell1.setCellValue(new HSSFRichTextString(tel));

		// 第一行第liu列
		cell1 = row1.createCell((short)5);
		cell1.setCellStyle(cellStyleTitle);
		cell1.setCellValue(new HSSFRichTextString(sex));

		// 第一行第qi列
		cell1 = row1.createCell((short)6);
		cell1.setCellStyle(cellStyleTitle);
		cell1.setCellValue(new HSSFRichTextString(sex));

		// 定义第二行
		HSSFRow row = sheet.createRow(2);
		HSSFCell cell = row.createCell((short)1);
		StudentDTO student = new StudentDTO();
		for (int i = 0; i < list.size(); i++) {
			student = list.get(i);
			row = sheet.createRow(i + 2);

			cell = row.createCell((short)0);
			cell.setCellStyle(cellStyle);
			cell.setCellValue(new HSSFRichTextString(student.getId() + ""));

			cell = row.createCell((short)1);
			cell.setCellStyle(cellStyle);
			cell.setCellValue(new HSSFRichTextString(student.getName()));

			cell = row.createCell((short)2);
			cell.setCellStyle(cellStyle);
			cell.setCellValue(new HSSFRichTextString(student.getAge() + ""));

//			cell = row.createCell((short)3);
//			cell.setCellStyle(cellStyle);
//			cell.setCellValue(
//					new HSSFRichTextString(student.getAddress() + ""));
//
//			cell = row.createCell((short)4);
//			cell.setCellStyle(cellStyle);
//			cell.setCellValue(new HSSFRichTextString(student.getAddress()));
//
//			cell = row.createCell((short)5);
//			cell.setCellStyle(cellStyle);
//			cell.setCellValue(new HSSFRichTextString(student.getTel()));

			cell = row.createCell((short)6);
			cell.setCellValue(
					new HSSFRichTextString(String.valueOf(student.getSex())));
			cell.setCellStyle(cellStyle);

		}
		try {
			bufferedOutPut.flush();
			wb.write(bufferedOutPut);
			bufferedOutPut.close();
		} catch (IOException e) {
			e.printStackTrace();
			System.out.println("Output   is   closed ");
		} finally {
			list.clear();
		}
	}

}
